package com.hele.sts.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.hele.sts.pub.ConnectDB;
import com.hele.sts.pub.SpringContextHolder;

public class TifDao {

	private static final Logger logger = LoggerFactory.getLogger(TifDao.class);

	public static TifInfo getTifInfo(String nbxh,String jnmlbh,String inmlbh) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = null;
		ConnectDB connsyb = null;
		ResultSet rs = null;
		TifInfo tifInfo = null;
		String lsSql = " select trim(CLMLMC) CLMLMC,trim(MLYM) MLYM,trim(WJLJMC) WJLJMC,trim(INMLQX) INMLQX,"
				+ " trim(JTBZ) JTBZ,trim(SERVERNAME) SERVERNAME,trim(DISKNAME) DISKNAME,trim(IPADDRESS) IPADDRESS,CZBZ,"
				+ "  trim(SERVERDISK) SERVERDISK,IPADDRESS,FTPUSER,FTPPASSWORD,da_qyjnmlb.SERVERXH as SERVERXH from da_qyjnmlb,xt_server "
				+ " where xt_server.SERVERXH = da_qyjnmlb.SERVERXH and xt_server.djjg = da_qyjnmlb.djjg   and xt_server.strategy='0' "
				+ " and nbxh=? and jnmlbh=? and inmlbh=?";
		logger.debug(lsSql);
		connsyb = SpringContextHolder.getBean(ConnectDB.class);
		
		try {
			conn = connsyb.getConnection();
			pstmt = conn.prepareStatement(lsSql);
			pstmt.setString(1, nbxh);
			pstmt.setString(2, jnmlbh);
			pstmt.setString(3, inmlbh);
			rs = pstmt.executeQuery();
		    if (rs.next()) {
		    	tifInfo = rsToTifInfo(rs);
		    }else{
		    	tifInfo = new TifInfo();
		    }
		    return tifInfo;
		} catch (SQLException e) {
			logger.info("db error", e);
		} finally {
			DbClose.freeResultSet(rs);
			DbClose.freePreparedStatement(pstmt);
			DbClose.freeConnection(conn);
		}

		return null;
	}

	private static TifInfo rsToTifInfo(ResultSet rs) throws SQLException{
		TifInfo tifInfo = new TifInfo();
		tifInfo.setSourceFileName(rs.getString("WJLJMC"));
		tifInfo.setIpAddress(rs.getString("IPADDRESS"));
		tifInfo.setFtpuser(rs.getString("FTPUSER"));
		tifInfo.setFtppassword(rs.getString("FTPPASSWORD"));
		tifInfo.setServerxh(rs.getInt("SERVERXH"));
		tifInfo.setCzbz(rs.getString("CZBZ"));
		return tifInfo;
	}

	public static List<Qyclyqxb> searchClyqxList(String nbxh, String jnmlbh, String inmlbh) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = null;
		ConnectDB connsyb = null;
		ResultSet rs = null;
		List<Qyclyqxb> list = new ArrayList<Qyclyqxb>();
		String lsSql = " select * from da_qyclyqxb where nbxh=? and jnmlbh=? and inmlbh=?";
		logger.debug(lsSql);
		connsyb = SpringContextHolder.getBean(ConnectDB.class);
		
		try {
			conn = connsyb.getConnection();
			pstmt = conn.prepareStatement(lsSql);
			pstmt.setString(1, nbxh);
			pstmt.setString(2, jnmlbh);
			pstmt.setString(3, inmlbh);
			rs = pstmt.executeQuery();
		    while (rs.next()) {
		    	Qyclyqxb a = rsToQyclyqxb(rs);
		    	list.add(a);
		    }
		    return list;
		} catch (SQLException e) {
			logger.info("db error", e);
		} finally {
			DbClose.freeResultSet(rs);
			DbClose.freePreparedStatement(pstmt);
			DbClose.freeConnection(conn);
		}

		return list;
	}

	private static Qyclyqxb rsToQyclyqxb(ResultSet rs) throws SQLException {
		Qyclyqxb t = new Qyclyqxb();
		t.setNbxh(rs.getString("nbxh"));
		t.setJnmlbh(rs.getInt("jnmlbh"));
		t.setInmlbh(rs.getInt("inmlbh"));
		t.setYh(rs.getInt("yh"));
		t.setYm(rs.getInt("ym"));
		t.setYqx(rs.getString("yqx"));
		return t;
	}
}
